drop table jms_tmp.dm_tms_road_waybill_linepart_sum_dt;
create external table jms_tmp.dm_tms_road_waybill_linepart_sum_dt
(
    shipment_no                       string comment '任务编号'
    ,shipment_name                    string comment '任务名称'
    ,line_cnt                         bigint comment '途径地数量'
    ,line_grade                       bigint comment '等级'
    ,vehicleline_code                 string comment '参考车线编码'
    ,vehicleline_name                 string comment '参考车线名称'
    ,linepart_name                    string comment '线路段名称'
    ,mileage                          string comment '系统里程'
    ,linepart_mileage                 decimal(10,2) comment '线路段里程'
    ,business_attribute               string comment '任务类型 1正班2加班'
    ,has_out                          string comment '是否兜底线路'
    ,strategy_line                    string comment '是否战略线路'
    ,operation_model                  string comment '运行模式 1单边2对开3往返'
    ,plate_number                     string comment '车牌号'
    ,vehicletype_id                   string comment '车型ID'
    ,vehicletype_name                 string comment '车型'
    ,axle_number                      int    comment '车辆轴数'
    ,shipno_planned_departure_time    timestamp comment '任务单规划发车时间'
    ,shipno_actual_departure_time     timestamp comment '任务单实际发车时间'
    ,shipno_planned_arrival_time      timestamp comment '任务单规划到车时间'
    ,shipno_actual_arrival_time       timestamp comment '任务单实际到车时间'
    ,linepart_planned_departure_time  timestamp comment '线路段规划发车时间'
    ,linepart_actual_departure_time   timestamp comment '线路段实际发车时间'
    ,linepart_planned_arrival_time    timestamp comment '线路段规划到车时间'
    ,linepart_actual_arrival_time     timestamp comment '线路段实际到车时间'
    ,start_site_code                  string comment '出发站点编号'
    ,start_site_name                  string comment '出发站点名称'
    ,start_site_sort_num              string comment '出发站点顺序编号'
    ,end_site_code                    string comment '目的站点编码'
    ,end_site_name                    string comment '目的站点名称'
    ,end_site_sort_num                string comment '目的站点顺序编号'
    ,start_code                       string comment '始发中心编号'
    ,start_name                       string comment '始发中心名称'
    ,end_code                         string comment '目的中心编号'
    ,end_name                         string comment '目的中心名称'
    ,linepart_count                   bigint comment '线路段装载票数'
    ,linepart_weight                  decimal(15,5) comment '线路段装载重量(吨)'
    ,linepart_volume                  decimal(15,5) comment '线路段装载体积(m3)'
    ,weight0_count                    bigint comment '重量段0数量(w=0kg)'
    ,weight1_count                    bigint comment '重量段1数量(0<w<=30kg)'
    ,weight2_count                    bigint comment '重量段2数量(30<w<1000kg)'
    ,weight3_count                    bigint comment '重量段3数量(w>=1000kg)'
    ,min_start_site_sort_num          bigint comment '最小发件站点顺序编号'
    ,min_end_site_sort_num            bigint comment '最小下一站顺序编号'
    ,vehiclevolume                    decimal(15,5) comment '核载体积m3'
    ,vehicleweight                    decimal(15,5) comment '核载重量(单位：吨)'
    ,vehiclecount                     decimal(15,5) comment '核载票数'
    ,averagevolume                    decimal(10,5) comment '平均单票体积'
    ,start_province_id                string comment '始发省份ID'
    ,start_province_name              string comment '始发省份名称'
    ,start_region_id                  string comment '始发大区ID'
    ,start_region_name                string comment '始发大区名称'
    ,start_agent_code                 string comment '始发代理区ID'
    ,start_agent_name                 string comment '始发代理区名称'
    ,end_province_id                  string comment '目的省份ID'
    ,end_province_name                string comment '目的省份名称'
    ,end_region_id                    string comment '目的大区ID'
    ,end_region_name                  string comment '目的大区名称'
    ,end_agent_code                   string comment '目的代理区ID'
    ,end_agent_name                   string comment '目的代理区名称'
    ,start_site_province_id           string comment '始发站点省份ID'
    ,start_site_province_name         string comment '始发站点省份名称'
    ,start_site_region_id             string comment '始发站点大区ID'
    ,start_site_region_name           string comment '始发站点大区名称'
    ,start_site_agent_code            string comment '始发站点代理区ID'
    ,start_site_agent_name            string comment '始发站点代理区名称'
    ,end_site_province_id             string comment '目的站点省份ID'
    ,end_site_province_name           string comment '目的站点省份名称'
    ,end_site_region_id               string comment '目的站点大区ID'
    ,end_site_region_name             string comment '目的站点大区名称'
    ,end_site_agent_code              string comment '目的站点代理区ID'
    ,end_site_agent_name              string comment '目的站点代理区名称'
    ,line_type                        int    comment '线路类型1短驳2直发3经停'
    ,error_scan_count                 bigint comment '错扫票数'
    ,cycle_bag_cnt                    bigint comment '循环袋数量'
    ,cycle_bag_weight                 bigint comment '循环袋重量'
    ,issupplies                       int    comment '是否物资线路1是0否'
)
comment '临时表-线路段装载票数'
stored as parquet
location '/dw/hive/jms_tmp.db/external/dm_tms_road_waybill_linepart_sum_dt'
;




drop table jms_dm.dm_tms_shipment_line_load_rate_dt;
create external table jms_dm.dm_tms_shipment_line_load_rate_dt
(
     shipment_no                         string comment '任务单号'
    ,shipment_name                       string comment '线路名称'
    ,date_time                           date   comment '运行日期'
    ,vehicleline_code                    string comment '线路编码'
    ,vehicleline_name                    string comment '参考车线名称'
    ,linepart_name                       string comment '线路段'
    ,mileage                             string comment '任务单公里数'
    ,linepart_mileage                    decimal(10,2) comment '线路段公里数'
    ,business_attribute                  string comment '任务类型 1正班2加班'
    ,has_out                             string comment '是否兜底1是2否'
    ,strategy_line                       string comment '否战略线路1是2否'
    ,operation_model                     string comment '运行模式 1单边2对开3往返'
    ,plate_number                        string comment '车牌号'
    ,vehicletype_id                      string comment '车型ID'
    ,vehicletype_name                    string comment '车型名称'
    ,axle_number                         int    comment '车辆轴数'
    ,shipno_planned_departure_time       timestamp comment '任务单规划发车时间'
    ,shipno_actual_departure_time        timestamp comment '任务单实际发车时间'
    ,shipno_planned_arrival_time         timestamp comment '任务单规划到车时间'
    ,shipno_actual_arrival_time          timestamp comment '任务单实际到车时间'
    ,linepart_planned_departure_time     timestamp comment '线路段规划发车时间'
    ,linepart_actual_departure_time      timestamp comment '线路段实际发车时间'
    ,linepart_planned_arrival_time       timestamp comment '线路段规划到车时间'
    ,linepart_actual_arrival_time        timestamp comment '线路段实际到车时间'
    ,start_site_code                     string comment '始发站点编号'
    ,start_site_name                     string comment '始发站点名称'
    ,start_site_sort_num                 string comment '始发站点顺序编号'
    ,end_site_code                       string comment '站点发件下一站编码'
    ,end_site_name                       string comment '站点发件下一站名称'
    ,end_site_sort_num                   string comment '目的站点顺序编号'
    ,start_code                          string comment '始发中心编号'
    ,start_name                          string comment '始发中心名称'
    ,end_code                            string comment '目的中心编号'
    ,end_name                            string comment '目的中心名称'

    ,linepart_count                      bigint comment '装载票数'
    ,plan_count                          bigint comment '核载票数'
    ,linepart_rate                       decimal(15,2) comment '线路段装载率'
    ,line_count                          bigint comment '线路装载票数'
    ,first_site_plan_linepart_count      bigint comment '首站核载票数'
    ,line_rate                           decimal(15,2) comment '线路装载率'

    ,linepart_weight                     decimal(15,2) comment '装载重量'
    ,plan_weight                         decimal(15,2) comment '核载重量'
    ,linepart_weight_rate                decimal(15,2) comment '线路段重量装载率'
    ,line_weight                         decimal(15,2) comment '线路装载重量'
    ,first_site_plan_linepart_weight     decimal(15,2) comment '首站核载重量'
    ,line_weight_rate                    decimal(15,2) comment '线路重量装载率'

    ,linepart_volume                     decimal(15,4) comment '装载体积'
    ,plan_volume                         decimal(15,4) comment '核载体积'
    ,linepart_volume_rate                decimal(15,2) comment '线路段体积装载率'
    ,line_volume                         decimal(15,4) comment '线路装载体积'
    ,first_site_plan_linepart_volume     decimal(15,4) comment '首站核载体积'
    ,line_volume_rate                    decimal(15,2) comment '线路体积装载率'

    ,start_province_id                   string comment '始发省份ID'
    ,start_province_name                 string comment '始发省份名称'
    ,start_region_id                     string comment '始发大区ID'
    ,start_region_name                   string comment '始发大区名称'
    ,start_agent_code                    string comment '始发代理区ID'
    ,start_agent_name                    string comment '始发代理区名称'
    ,end_province_id                     string comment '目的省份ID'
    ,end_province_name                   string comment '目的省份名称'
    ,end_region_id                       string comment '目的大区ID'
    ,end_region_name                     string comment '目的大区名称'
    ,end_agent_code                      string comment '目的代理区ID'
    ,end_agent_name                      string comment '目的代理区名称'
    ,start_site_province_id              string comment '始发站点省份ID'
    ,start_site_province_name            string comment '始发站点省份名称'
    ,start_site_region_id                string comment '始发站点大区ID'
    ,start_site_region_name              string comment '始发站点大区名称'
    ,start_site_agent_code               string comment '始发站点代理区ID'
    ,start_site_agent_name               string comment '始发站点代理区名称'
    ,end_site_province_id                string comment '目的站点省份ID'
    ,end_site_province_name              string comment '目的站点省份名称'
    ,end_site_region_id                  string comment '目的站点大区ID'
    ,end_site_region_name                string comment '目的站点大区名称'
    ,end_site_agent_code                 string comment '目的站点代理区ID'
    ,end_site_agent_name                 string comment '目的站点代理区名称'
    ,weight0_count                       bigint comment '重量段0数量(w=0kg)'
    ,weight1_count                       bigint comment '重量段1数量(0<w<=30kg)'
    ,weight2_count                       bigint comment '重量段2数量(30<w<1000kg)'
    ,weight3_count                       bigint comment '重量段3数量(w>=1000kg)'
    ,line_type                           int    comment '线路类型1短驳2直发3经停'
    ,error_scan_flag                     int    comment '错扫标识0无错扫1有错扫'
    ,error_scan_count                    bigint comment '错扫票数'
    ,cycle_bag_cnt                       bigint comment '循环袋数量'
    ,cycle_bag_weight                    bigint comment '循环袋重量'
    ,issupplies                          int    comment '是否物资线路1是0否'
    ,should_cycle_bag_cnt                bigint comment '应装循环袋数量'
)
comment '干线线路装载率'
partitioned by (dt string comment '规划发车日期分区')
stored as parquet
location '/dw/hive/jms_dm.db/external/dm_tms_shipment_line_load_rate_dt'
;
